﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using OrderManager.DataAccess.Extentions;
using OrderManager.Domain.Entities;
using OrderManager.Domain.Repositories;

namespace OrderManager.DataAccess.Repositories
{
    /// <summary>
    /// Provides actions to manipulate [Customer] table in database. Implements <see cref="OrderManager.Domain.Repositories.ICustomerRepository"/> interface
    /// </summary>
    public class CustomerRepository : ICustomerRepository
    {
        #region Implementation of ICustomerRepository

        /// <summary>
        /// Adds new customer to database with specified name and address
        /// </summary>
        /// <param name="name">Customer name</param>
        /// <param name="address">Customer address</param>
        /// <returns><see cref="OrderManager.Domain.Entities.Customer"/> with specified parameters</returns>
        public Customer Create(string name, string address)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                string sql = "INSERT INTO Sales.[Customer]" +
                    "(name, address) VALUES" +
                    "(@name, @address); SELECT SCOPE_IDENTITY();";

                int customerId;
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@name", name, SqlDbType.VarChar);
                    cmd.AddParameter("@address", address, SqlDbType.VarChar);

                    customerId = Convert.ToInt32(cmd.ExecuteScalar());
                }
                return new Customer(customerId, name, address);
            }
        }

        /// <summary>
        /// Deletes customer with specified id from database 
        /// </summary>
        /// <param name="id">Id of customer to delete</param>
        public void Delete(Int32 id)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                using (SqlTransaction sqlTransaction = uow.Connection.BeginTransaction())
                {
                    string deleteOrders = "DELETE FROM  Sales.[Order] WHERE customer_id=@customer_id";

                    using (SqlCommand cmd = new SqlCommand(deleteOrders, uow.Connection))
                    {
                        cmd.Transaction = sqlTransaction;

                        cmd.AddParameter("@customer_id", id, SqlDbType.Int);

                        cmd.ExecuteNonQuery();
                    }

                    string sqlDeleteCustomer = "DELETE FROM Sales.[Customer] WHERE customer_id = @customer_id";

                    using (SqlCommand cmd = new SqlCommand(sqlDeleteCustomer, uow.Connection))
                    {
                        cmd.Transaction = sqlTransaction;

                        cmd.AddParameter("@customer_id", id, SqlDbType.Int);

                        cmd.ExecuteNonQuery();

                    }
                    sqlTransaction.Commit();
                }
            }
        }

        /// <summary>
        /// Gets a list of all customers in database
        /// </summary>
        /// <returns>Collection of <see cref="OrderManager.Domain.Entities.Customer"/> which contain all customers from database</returns>
        public List<Customer> GetAll()
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                List<Customer> customers = new List<Customer>();
                string sql = "SELECT customer_id, name, address FROM Sales.[Customer]";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var customerId = Convert.ToInt32(dr["customer_id"]);
                            var customerName = Convert.ToString(dr["name"]);
                            var customerAddress = Convert.ToString(dr["address"]);
                            customers.Add(new Customer(customerId, customerName, customerAddress));
                        }
                    }
                }
                return customers;
            }
        }

        /// <summary>
        /// Gets a customer with specified id from database 
        /// </summary>
        /// <param name="id">Id of customer to get</param>
        /// <returns><see cref="OrderManager.Domain.Entities.Customer"/> with specified id (name and address from database)</returns>
        public Customer GetById(int id)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                Customer customer;
                string sql = "SELECT customer_id, name, address FROM Sales.[Customer] WHERE customer_id = @customer_id";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@customer_id", id, SqlDbType.Int);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        var customerId = Convert.ToInt32(dr["customer_id"]);
                        var customerName = Convert.ToString(dr["name"]);
                        var customerAddress = Convert.ToString(dr["address"]);
                        customer = new Customer(customerId, customerName, customerAddress);
                    }
                }
                return customer;
            }
        }

        /// <summary>
        /// Updates the entity of customer in database with values of properties of specified Customer
        /// </summary>
        /// <param name="customer">Instance of <see cref="OrderManager.Domain.Entities.Customer"/> class with new values</param>
        public void Update(Customer customer)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                string sql = "UPDATE Sales.[Customer] SET name=@name,address=@address WHERE customer_id=@customer_id";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@name", customer.Name, SqlDbType.VarChar);
                    cmd.AddParameter("@address", customer.Address, SqlDbType.VarChar);
                    cmd.AddParameter("@customer_id", customer.Id, SqlDbType.Int);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion
    }
}
